package com.aimee.dao;

import com.aimee.entity.Course;
import com.aimee.entity.Questions;
import com.aimee.entity.User;
import com.aimee.util.Dbutil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CourseDao {

    /**
     * 分页查询所有的题库（科目）
     *
     * @param page
     * @return
     * @throws SQLException
     */
    public List showAllCourse(int page) throws SQLException {
        String sql = "select * from course limit ? , 10";
        Connection conn = Dbutil.getConn();
        int t = (page - 1) * 10;
        QueryRunner runner = new QueryRunner();
        List list = runner.query(conn, sql, t, new BeanListHandler<Course>(Course.class));
        conn.close();
        return list;
    }

    /**
     * 查询所有的题库（科目） 不分页
     *
     * @return
     * @throws SQLException
     */
    public List<User> show() throws SQLException {
        String sql = "select * from course";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        List list = runner.query(conn, sql, new BeanListHandler<Course>(Course.class));
        conn.close();
        return list;
    }


    /**
     * 单个新增题库（课程）
     *
     * @param course
     * @return
     * @throws SQLException
     */
    public int addCourse(Course course) throws SQLException {
        String sql = "INSERT INTO course (course_name,course_desc) VALUES (?,?)";
        Connection conn = Dbutil.getConn();
        String[] paras = new String[2];
        paras[0] = course.getCourse_name();
        paras[1] = course.getCourse_desc();
        QueryRunner runner = new QueryRunner();
        int n = runner.update(conn, sql, paras);
        conn.close();
        return n;
    }

    /**
     * 修改题库（科目）
     *
     * @param course
     * @return
     * @throws SQLException
     */
    public int updateCourse(Course course) throws SQLException {
        String sql = "UPDATE course SET course_name =? ,course_desc = ? WHERE course_id = ?";
        Connection conn = Dbutil.getConn();
        String[] paras = new String[3];
        paras[0] = course.getCourse_name();
        paras[1] = course.getCourse_desc();
        paras[2] = course.getCourse_id() + "";
        QueryRunner runner = new QueryRunner();
        int n = runner.update(conn, sql, paras);
        conn.close();
        return n;
    }

    /**
     * 删除题库（科目）
     *
     * @param course_id
     * @return
     * @throws SQLException
     */
    public int deleteCourse(int course_id) throws SQLException {
        String sql = "DELETE FROM course WHERE course_id =?";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        int n = runner.update(conn, sql, course_id);
        conn.close();
        return n;
    }

    /**
     * 批量导入题目到指定的题库
     *
     * @param questions
     * @return
     * @throws SQLException
     */
    public int addQuestionsIntoThisCourse(Questions questions) throws SQLException {
        String sql = "INSERT INTO questions (course_id,user_id,question_content,question_img,question_score," +
                "question_type,question_optiona,question_optionb,question_optionc,question_optiond,question_answer,update_time)" +
                "VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        Object parase[] = new Object[12];
        parase[0] = questions.getCourse_id();
        parase[1] = questions.getUser_id();
        parase[2] = questions.getQuestion_content();
        parase[3] = questions.getQuestion_img();
        parase[4] = questions.getQuestion_score();
        parase[5] = questions.getQuestion_type();
        parase[6] = questions.getQuestion_optiona();
        parase[7] = questions.getQuestion_optionb();
        parase[8] = questions.getQuestion_optionc();
        parase[9] = questions.getQuestion_optiond();
        parase[10] = questions.getQuestion_answer();
        parase[11] = questions.getUpdate_time();

        int n = runner.update(conn, sql, parase);
        conn.close();
        return n;

    }

    /**
     * 查询所有的科目名称
     *
     * @return
     * @throws SQLException
     */
    public ArrayList showCourseNames() throws SQLException {
        String sql = "SELECT course_name  FROM course;";
        Connection conn = Dbutil.getConn();
        PreparedStatement pres = conn.prepareStatement(sql);
        ResultSet resultSet = pres.executeQuery();
        ArrayList list = new ArrayList();
        while (resultSet.next()) {
            String item = resultSet.getString(1);
            list.add(item);
        }

        QueryRunner runner = new QueryRunner();
//        Object[] list = runner.query(conn, sql, new ArrayHandler());
        conn.close();
        return list;
    }

    /**
     * 根据科目名称得到科目的id
     *
     * @param courseName
     * @return
     * @throws SQLException
     */
    public int getCoureseId(String courseName) throws SQLException {
        String sql = "SELECT course_id from course WHERE course_name=?";
        Connection conn = Dbutil.getConn();
        PreparedStatement pres = conn.prepareStatement(sql);
        pres.setString(1, courseName);
        ResultSet resultSet = pres.executeQuery();
        int id = 0;
        while (resultSet.next()) {
            id = resultSet.getInt(1);
        }
        conn.close();
        return id;
    }


    public static void main(String[] args) {
        CourseDao courseDao = null;
        Questions question = null;

        try {
//            System.out.println(courseDao.showAllCourse(1));

//            course = new Course(10004, "PHP教程", "针对web开发者的另一种语言");
//            System.out.println(courseDao.addCourse(course));

//            System.out.println(courseDao.updateCourse(course));

//            System.out.println(courseDao.deleteCourse(10004));

            courseDao = new CourseDao();
//            question = new Questions(1001, 10001, 100001, "JavaIO的作用",
//                    "1", 2, "1", "this", "1", "class", "that", "that",
//                    new Date(System.currentTimeMillis()));
            System.out.println(courseDao.getCoureseId("算法"));
//            System.out.println(question);
//            System.out.println(courseDao.addQuestionsIntoThisCourse(question));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
